package com.business.commons.report.excel;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.DirectoryEntry;
import org.apache.poi.poifs.filesystem.DocumentEntry;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.business.commons.date.DateFormat;

public class ExcelOp<T> {
	
	/**
	 * 生成excel且可下载
	 * 
	 * @param title
	 *            页标题
	 * @param rowTitle
	 *            头标题
	 * @param list
	 *            数据
	 * @param fileName
	 *            文件名
	 * @return
	 * @throws Exception
	 */
	public void Excel(String title, String[] rowTitle, String []dataSort, List<T> list, String fileName,HttpServletResponse response) throws Exception {
		response.reset();// 清空输出流
		response.setHeader("Content-disposition", "attachment; filename= " + fileName + ".xls");
		response.setContentType("application/msexcel");
		OutputStream fileOut = response.getOutputStream();
		T t = list.get(0);
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet(title);
		sheet.setColumnWidth(0, 20 * 256);
		sheet.setDefaultColumnWidth(20);  
		Class c = t.getClass();
		Method m[] = c.getDeclaredMethods();

		Font font = wb.createFont();
		//font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//  加粗
		CellStyle cstyle = wb.createCellStyle();
		// 居中对齐
		cstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cstyle.setFillBackgroundColor(HSSFColor.GREEN.index);
		cstyle.setFont(font);
		// 首行填充
		Row firstRow = sheet.createRow(0);
		int findex = 0;
		for (String f : rowTitle) {
			HSSFCell cell1 = (HSSFCell) firstRow.createCell((short) findex++);
			cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell1.setCellStyle(cstyle);
			cell1.setCellValue(f);
		}
		// 内容填充
		for (int i = 0; i < list.size(); i++) {
			Row row = sheet.createRow(i + 1);
			T tt = (T) list.get(i);
			int index = 0;
			HashMap<String,String> map = new HashMap<String,String>();
			for (int ii = 0; ii < m.length; ii++) {
				if (m[ii].getName().startsWith("get")) {
					String name = m[ii].getReturnType().getName();
					if("java.util.Date".equals(name)) {
						Date d = (Date)m[ii].invoke(tt, (Object[]) null);
						String theDate = null == d ? "" : DateFormat.getTheTime(d);
						map.put(m[ii].getName().toLowerCase().substring(3), theDate);
					} else {
						map.put(m[ii].getName().toLowerCase().substring(3), String.valueOf(m[ii].invoke(tt, (Object[]) null)));
					}
				}
			}
			for(String name : dataSort){
				HSSFCell cell = (HSSFCell) row.createCell((short) index++);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(map.get(name.toLowerCase()));
			}
		}

		wb.write(fileOut);
		fileOut.flush();
		fileOut.close();
	}

	/**
	 * 生成excel且可下载
	 * 
	 * @param title
	 *            页标题
	 * @param list
	 *            数据
	 * @param fileName
	 *            文件名
	 * @return
	 * @throws Exception
	 */
	public void Excel(String title, List<T> list, String fileName,HttpServletResponse response) throws Exception {
		response.reset();// 清空输出流
		response.setHeader("Content-disposition", "attachment; filename= " + fileName + ".xls");
		response.setContentType("application/msexcel");
		OutputStream fileOut = response.getOutputStream();
		T t = list.get(0);
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet(title);
		Class c = t.getClass();
		Method m[] = c.getDeclaredMethods();

		Font font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
		CellStyle cstyle = wb.createCellStyle();
		// 居中对齐
		cstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cstyle.setFillBackgroundColor(HSSFColor.GREEN.index);
		cstyle.setFont(font);
		// 首行填充
		Field[] fx = c.getDeclaredFields();
		Row firstRow = sheet.createRow(0);
		int findex = 0;
		for (Field f : fx) {
			if (!f.getName().contains("serialVersionUID")) {
				HSSFCell cell1 = (HSSFCell) firstRow.createCell((short) findex++);
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setCellStyle(cstyle);
				cell1.setCellValue(String.valueOf(f.getName()));
			}
		}
		// 内容填充
		for (int i = 0; i < list.size(); i++) {
			Row row = sheet.createRow(i + 1);
			T tt = (T) list.get(i);
			int index = 0;
			for (int ii = 0; ii < m.length; ii++) {
				if (m[ii].getName().contains("get")) {
					HSSFCell cell = (HSSFCell) row.createCell((short) index++);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(String.valueOf(m[ii].invoke(tt, (Object[]) null)));
				}
			}
		}

		wb.write(fileOut);
		fileOut.flush();
		fileOut.close();
	}

	public void readExcel(File file) {
		// File f = new File("c:\\a.xls");
		File f = file;
		List list = new ArrayList();
		try {
			FileInputStream is = new FileInputStream(f);
			HSSFWorkbook wbs = new HSSFWorkbook(is);// 创建工作薄
			HSSFSheet childSheet = wbs.getSheetAt(0);// 得到工作表
			int totalRow = childSheet.getLastRowNum();// 得到excel的总记录条数
			// HSSFRow row = null;//对应excel的行
			// HSSFCell cell = null;//对应excel的列

			System.out.println("有行数" + childSheet.getLastRowNum());
			for (int j = 0; j < totalRow; j++) {
				HSSFRow row = childSheet.getRow(j);
				String rowStr = "(";
				if (null != row) {
					for (int k = 0; k < row.getLastCellNum(); k++) {
						HSSFCell cell = row.getCell(k);
						if (null != cell) {

							switch (cell.getCellType()) {
							case HSSFCell.CELL_TYPE_NUMERIC: // 数字
								System.out.print(cell.getNumericCellValue() + "   ");
								break;
							case HSSFCell.CELL_TYPE_STRING: // 字符串
								System.out.print(cell.getStringCellValue() + "   ");
								break;
							case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
								System.out.println(cell.getBooleanCellValue() + "   ");
								break;
							case HSSFCell.CELL_TYPE_FORMULA: // 公式
								System.out.print(cell.getCellFormula() + "   ");
								break;
							case HSSFCell.CELL_TYPE_BLANK: // 空值
								System.out.println(" ");
								break;
							case HSSFCell.CELL_TYPE_ERROR: // 故障
								System.out.println(" ");
								break;
							default:
								System.out.print("未知类型   ");
								break;
							}
						} else {
							System.out.print("-   ");
						}
					}
				}
				rowStr += ")";
				System.out.println();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 导出word文档 只能是文本
	 *
	 * @param content
	 *            内容
	 * @param fileName
	 *            文件名
	 * @return
	 */
	public OutputStream writeWordFile(String content, String fileName,HttpServletResponse response) {
		response.reset();// 清空输出流
		response.setHeader("Content-disposition", "attachment; filename= " + fileName + ".xls");
		response.setContentType("application/msexcel");
		OutputStream ostream = null;
		try {
			ostream = response.getOutputStream();
			byte b[] = content.getBytes();
			ByteArrayInputStream bais = new ByteArrayInputStream(b);
			POIFSFileSystem fs = new POIFSFileSystem();
			DirectoryEntry directory = fs.getRoot();
			DocumentEntry de = directory.createDocument("WordDocument", bais);
			System.out.println(de.getName());
			fs.writeFilesystem(ostream);
			bais.close();
			ostream.close();
		} catch (IOException e) {
			System.out.println("类 ExcelOp类中word导出有异常：" + e.getMessage());
		}
		return ostream;
	}
}
